#!/bin/bash
##
# Date: 2018-03-07
# Author: phiix
# Describe: Load large data table,like table above 2G size.
# first: create database and table .
# second: load table structure.
# third: load table data.
##

export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
export LANG=en_US.UTF-8

if [ $# -ne 2 ];then
    echo "Usage: $0 <database_name> <table_name>"
    exit 1
fi

database_name=$1
table_name=$2
source_dir="/data/backup_dir"


alias mysql='mysql -S /var/lib/mysql/mysql.sock'
# check database and table
echo "Check database ${database_name} and table ${table_name} exist or not."
mysql -BN -e "show databases;" |grep -Piq "${database_name}"
if [ $? -ne 0 ];then
    echo "${database_name} not exists."
    read -t 30 -p "Create database ${database_name} ? [y/n] (default: n)  " reply
    if [ "${reply:-n}" == "y" ];then
	mysql -e "create database ${database_name};"
    else
	echo "Do not have database to load table.Exit..."
	exit 1
    if
else
    echo "${database_name} exists."    
    read -t 30 -p "Whether to recreate the database ${database_name} ? [y/n] (default: n)  " reply
    if [ "${reply:-n}" == "y" ];then
        echo "drop and recreate database ${database_name} ."
	mysql -e "drop database ${database_name};"
	mysql -e "create database ${database_name};"
	mysql -BN -e "show databases;"|grep -Pq ${databases} && echo "create database ${database_name} complete."
    esle
	echo "will load table data into ${database_name} ."
    fi
fi

# load table structure and data into database.
echo "load table ${table_name} structure and data into database ${database_name}."
table_struc=$(ls ${source_dir}|grep ${table_name}_db0.sql)
if [ "x${table_struc}" != "x" ];then
    mysql -e "use ${database_name};source ${source_dir}/${table_struc};"
    index=$(mysql -BN -e "describe ${database_name}.${table_name};" |grep "PRI" |awk '{print $1}')
    if [ "x$index" != "x" ];then
	read -t 30 -p "the table index is ${index} ? [y/n] (default: y) " reply
	if [ "${reply:-y}" != "y" ];then
	    echo "the table index is error,check your dump sql file.Exit..."
	    exit 1
	else
	   echo "load table ${table_name} structure complete,OK!"
	fi
    else
	echo "the table index is error,check your dump sql file.Exit..."
	exit 1
    fi
else
    echo "not found table structure sql file.Exit..."
    exit 1
fi

echo "load table data."
for table_data in $(ls -rt ${source_dir} |grep ${index})
do
    echo "load ${table_data}."
    mysql -e "use ${database_name} ;load data infile \"${source_dir}/${table_data}\" into table ${table_name} fields terminated by ',' optionally enclosed by '\"' \
lines terminated by '\n' ;" 2>/dev/null
if [ $? -ne 0 ];then
    echo "load ${table_data} failed.Exit..."
    exit 1
else
    echo "load ${table_data} completed,OK!"
fi
done

unalias mysql
exit 0
